--  ===================================================

CREATE OR REPLACE PACKAGE BODY ZOEDEVOPS.ZOEPKG_PROJECT_OPS

-- Created in 2017.10.10 by polarbears
-- Copyright (c) 20xx, CHINA and/or affiliates.
-- All rights reserved.
--	Name:
-- 		zoepkg_ops_comm.pkb
--	Description:
-- 		运维管理控制节点公共功能包
--  Relation:
--      
--	Notes:
--		基本注意事项
--	修改 - （年-月-日） - 描述
--

 AS

-- ===================================================
-- 获取数据库版本
-- ===================================================
  FUNCTION GET_DB_VERSION RETURN VARCHAR2 
  AS
    lv_db_version VARCHAR2(80);
  BEGIN
    SELECT BANNER INTO lv_db_version FROM V$VERSION WHERE BANNER LIKE 'Oracle Database%';
    RETURN lv_db_version;
  END GET_DB_VERSION;

-- ===================================================
-- 获取远程管理数据库链路
-- ===================================================
  FUNCTION GET_DB_LINK(iv_db_id IN VARCHAR2) RETURN VARCHAR2 
  AS
    lv_db_link VARCHAR2(128);
  BEGIN
    SELECT DB_LINK_NAME INTO lv_db_link FROM DVP_PROJ_NODE_DB_LINKS WHERE DB_ID# = iv_db_id;
    RETURN lv_db_link;
  END GET_DB_LINK;

-- ===================================================
--  获取数据库基本信息
-- ===================================================
 FUNCTION GET_DB_BASIC_INFO RETURN zoett_db_basic_info AS
   lt_db_basic_info zoett_db_basic_info := zoett_db_basic_info();      --数据库基本信息对象
   lv_is_cdb             VARCHAR2(3);          --是否容器
   lv_db_version         VARCHAR2(3);         --数据库软件版本号
   ld_db_created_time    DATE;                 --数据库创建时间
   lv_sql                VARCHAR2(4000);       --SQL
  BEGIN
    lv_db_version := SUBSTR(GET_DB_VERSION(), 17,3);
    IF to_number(substr(lv_db_version,1,2)) >= 12 THEN
      lv_sql := 'select cdb from v$database';
      EXECUTE IMMEDIATE lv_sql INTO lv_is_cdb;
      IF lv_is_cdb = 'YES' THEN
        lv_sql := 'select creation_time from v$pdbs';
        EXECUTE IMMEDIATE lv_sql INTO ld_db_created_time;
      END IF;
    ELSE 
      select created into ld_db_created_time from v$database;
    END IF;
    lt_db_basic_info.extend;
    lt_db_basic_info(1) := zoeto_db_basic_info(null,null,null,null,null);
    lt_db_basic_info(1).server_name       := SYS_CONTEXT('USERENV', 'SERVER_HOST');
    lt_db_basic_info(1).server_ip_address      := utl_inaddr.get_host_address;
    lt_db_basic_info(1).db_name         := SYS_CONTEXT('USERENV', 'DB_NAME');
    lt_db_basic_info(1).created_time    := ld_db_created_time;
    lt_db_basic_info(1).db_version      := lv_db_version;
    RETURN lt_db_basic_info;
  END;
 
-- ===================================================
--  创建数据库用户或修改用户密码
-- ===================================================
PROCEDURE SET_DB_USER(iv_user_info IN VARCHAR2) AS
    lv_db_id        VARCHAR2(64);
    lv_username     VARCHAR2(64);
    lv_password     VARCHAR2(128);
    lv_secret_key   VARCHAR2(400);
    lv_sql          VARCHAR2(400);
    lv_sql_json      VARCHAR2(400);
    lv_remote_sql      VARCHAR2(4000);
    lv_encryp_password  VARCHAR2(128);
    ln_user_exist   NUMBER;
    lv_db_link     VARCHAR2(128);
  BEGIN
    SELECT JSON_VALUE(iv_user_info,'$.DB_ID'),
        JSON_VALUE(iv_user_info,'$.USERNAME'),
        JSON_VALUE(iv_user_info,'$.PASSWORD'),
        JSON_VALUE(iv_user_info,'$.SECRET_KEY')
    INTO lv_db_id,lv_username,lv_password,lv_secret_key
    FROM DUAL;
    lv_username := UPPER(lv_username);
    --生成随机密码
    IF lv_password is null THEN
      SELECT  DBMS_RANDOM.STRING('X',12) INTO lv_password FROM DUAL;
      lv_password := 'Zoe$'||lv_password;
    END IF;
    --  DB_ID不允许为空，为空异常处理。
    --  待实现
    IF lv_db_id IS NULL THEN
        
        RETURN;
    END IF;
    --  创建用户或修改密码
    lv_db_link := GET_DB_LINK(lv_db_id);
    lv_sql := 'select count(1) from dba_users@'||lv_db_link||' where username='''||lv_username||'''';
    EXECUTE  IMMEDIATE lv_sql INTO ln_user_exist;
    IF ln_user_exist = 1 THEN
        lv_sql_json := '{"SQL_TYPE":"DDL",';
        lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
        lv_sql_json := lv_sql_json ||'"ALTER USER '||lv_username||' IDENTIFIED BY '||lv_password||'"}';
    ELSE
        lv_sql_json := '{"SQL_TYPE":"DDL",';
        lv_sql_json := lv_sql_json ||'"SQL_TEXT":';
        lv_sql_json := lv_sql_json ||'"CREATE USER '||lv_username||' IDENTIFIED BY '||lv_password||'"}';
    END IF;
    lv_remote_sql :='BEGIN ZOEDEVOPS.ZOEPRC_EXEC_SQL@'||lv_db_link||'(:1); END;';
    EXECUTE IMMEDIATE lv_remote_sql using IN OUT lv_sql_json;
    --  保存用户与密码信息
    select count(1) INTO ln_user_exist from ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO where DB_ID# = lv_db_id and USERNAME = lv_username;
    select extend_key into lv_secret_key from ZOEDEVOPS.DVP_PROJECT_SECRET_KEY where key_id# = lv_db_id;
    lv_encryp_password := zoedevops.zoefun_encrypt_user(lv_username,lv_password,lv_secret_key,lv_db_id);
    IF ln_user_exist = 1 THEN
        UPDATE ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO SET
            (user_password,modifier_code,modified_time) =
            (select lv_encryp_password,'ZOEPKG_PROJECT_OPS.SAVE_DB_USER',SYSDATE from dual) 
        WHERE db_id# = lv_db_id and username = lv_username;
    ELSE
        INSERT INTO ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO 
            (project_id#,db_id#,username,user_password,creator_code,created_time) 
            SELECT project_id#,db_id,lv_username,lv_encryp_password,'ZOEPKG_PROJECT_OPS.SAVE_DB_USER',SYSDATE 
                FROM ZOEDEVOPS.DVP_PROJ_DB_BASIC_INFO 
                WHERE DB_ID = lv_db_id;
    END IF;
      dbms_output.put_line('not exist user');
    COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line('ERROR');
        ROLLBACK;
        RAISE;
  END SET_DB_USER;

-- ===================================================
--  保存数据库用户与密码
-- ===================================================
PROCEDURE SAVE_DB_USER(iv_user_info IN VARCHAR2) AS
    lv_db_id        VARCHAR2(64);
    lv_username     VARCHAR2(64);
    lv_password     VARCHAR2(128);
    lv_secret_key   VARCHAR2(400);
    lv_sql_ddl      VARCHAR2(400);
    lv_encryp_password  VARCHAR2(128);
    ln_user_exist   NUMBER;
  BEGIN
    SELECT JSON_VALUE(iv_user_info,'$.DB_ID'),
        JSON_VALUE(iv_user_info,'$.USERNAME'),
        JSON_VALUE(iv_user_info,'$.PASSWORD'),
        JSON_VALUE(iv_user_info,'$.SECRET_KEY')
    INTO lv_db_id,lv_username,lv_password,lv_secret_key
    FROM DUAL;
    lv_username := UPPER(lv_username);
    select count(1) INTO ln_user_exist from ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO where DB_ID# = lv_db_id and USERNAME = lv_username;
    select extend_key into lv_secret_key from ZOEDEVOPS.DVP_PROJECT_SECRET_KEY where key_id# = lv_db_id;
    lv_encryp_password := zoedevops.zoefun_encrypt_user(lv_username,lv_password,lv_secret_key,lv_db_id);
    IF ln_user_exist = 1 THEN
        UPDATE ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO SET
            (user_password,modifier_code,modified_time) =
            (select lv_encryp_password,'ZOEPKG_PROJECT_OPS.SAVE_DB_USER',SYSDATE from dual) 
        WHERE db_id# = lv_db_id and username = lv_username;
    ELSE
        INSERT INTO ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO 
            (project_id#,db_id#,username,user_password,creator_code,created_time) 
            SELECT project_id#,db_id,lv_username,lv_encryp_password,'ZOEPKG_PROJECT_OPS.SAVE_DB_USER',SYSDATE 
                FROM ZOEDEVOPS.DVP_PROJ_DB_BASIC_INFO 
                WHERE DB_ID = lv_db_id;
   END IF;
    COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
  END SAVE_DB_USER;

-- ===================================================
--  创建并保存数据库链路信息
-- ===================================================
  PROCEDURE SAVE_DB_LINK(iv_db_id IN VARCHAR2)
  AS
    lv_project_id VARCHAR2(128);
    lv_db_name  VARCHAR2(128);
    lv_server_ip_address    VARCHAR2(128);
    lv_db_link  VARCHAR2(128);
    lv_username VARCHAR2(128);
    lv_password VARCHAR2(128);
    lv_sql VARCHAR2(4000);
  BEGIN
    lv_username := 'ZOEAGENT';
    SELECT PROJECT_ID#,DB_NAME,SERVER_IP_ADDRESS# 
        INTO lv_project_id,lv_db_name,lv_server_ip_address 
        FROM ZOEDEVOPS.DVP_PROJ_DB_BASIC_INFO
        WHERE DB_ID=iv_db_id;
    SELECT zoedevops.zoefun_decrypt_user(lv_username,USER_PASSWORD,b.extend_key,iv_db_id)
        INTO lv_password
        FROM ZOEDEVOPS.DVP_PROJ_DB_USER_ADMIN_INFO a, ZOEDEVOPS.DVP_PROJECT_SECRET_KEY b
        WHERE a.DB_ID#=iv_db_id AND USERNAME = lv_username AND a.DB_ID#=b.key_id#;
    lv_db_link := lv_db_name||SUBSTR(lv_server_ip_address,INSTR(lv_server_ip_address,'.',-1)+1)||lv_username;
    lv_sql := 'create database link';
    lv_sql := lv_sql||' '||lv_db_link ;
    lv_sql := lv_sql||' connect to '||lv_username||' identified by '||lv_password;
    lv_sql := lv_sql||' using '''||lv_server_ip_address||'/'||lv_db_name||'''';
    EXECUTE IMMEDIATE lv_sql;
    INSERT INTO ZOEDEVOPS.DVP_PROJ_NODE_DB_LINKS 
        (PROJECT_ID#,DB_ID#,DB_LINK_NAME,CONNECT_TO_USER,CREATOR_CODE,CREATED_TIME)
        VALUES
        (lv_project_id,iv_db_id,lv_db_link,lv_username,SYS_CONTEXT('USERENV','SESSION_USER'),SYSDATE);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
  END SAVE_DB_LINK;
  
  -- ===================================================
--  创建并保存数据库链路信息
-- ===================================================
  PROCEDURE DROP_DB_LINK(iv_db_link IN VARCHAR2)
  AS
    lv_db_link  VARCHAR2(128);
    lv_sql VARCHAR2(4000);
  BEGIN
    lv_db_link := iv_db_link;
    lv_sql := 'drop database link';
    lv_sql := lv_sql||' '||lv_db_link ;
    EXECUTE IMMEDIATE lv_sql;
  EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
  END DROP_DB_LINK;
  
END ZOEPKG_PROJECT_OPS;

/

